package cn.mazhonghua.util;

import cn.mazhonghua.config.JFrameConfig;
import cn.mazhonghua.dao.ShopItemDao;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.List;

/**
 * @author JamesMaium
 * 数据库链接驱动 执行SQL操作类
 */
public class MysqlConnUtil {
    Connection connection = null;
    ResultSet resultSet = null;
    PreparedStatement stmt = null;

    public MysqlConnUtil() {
        try {
            Class.forName(JFrameConfig.DRIVER_NAME);
            connection = DriverManager.getConnection(JFrameConfig.LOCALHOST, JFrameConfig.USER_NAME, JFrameConfig.PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取下单用户名
     */
    public List<HashMap<String, String>> getTodayDatilUser(int currPage, int pageSize) throws SQLException {
        String format = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        String sql = "select datil_table.id as datilId,shop_table.title as title," +
                "shop_table.price as price,shop_table.type as type ," +
                "user_table.userTrueName as userTrueName,user_table.userName as userName,datil_table.count as count," +
                "datil_table.dateTime as datilTime from datil_table JOIN shop_table ON datil_table.shopId = shop_table.id JOIN user_table" +
                " ON user_table.id = datil_table.userId  where datil_table.dateTime BETWEEN '" + format + " 00:00:00' and '" + format + " 23:59:59' " +
                "order by datil_table.id desc limit " + (currPage - 1) * pageSize + ", " + pageSize;
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        List<HashMap<String, String>> mapList = new ArrayList<>();
        while (resultSet.next()) {
            HashMap<String, String> map = new HashMap<>();
            map.put("datilId", resultSet.getString("datilId"));
            map.put("shopTitle", resultSet.getString("title"));
            map.put("shopPrice", resultSet.getString("price"));
            map.put("shopType", resultSet.getString("type"));
            map.put("userTrueName", resultSet.getString("userTrueName"));
            map.put("userName", resultSet.getString("userName"));
            map.put("datilTime", resultSet.getString("datilTime"));
            map.put("count", resultSet.getString("count"));
            mapList.add(map);
        }
        resultSet.close();
        stmt.close();
        connection.close();
        return mapList;
    }


    /**
     * 获取今日下单用户数量
     */
    public int getDatilUserCount() throws SQLException {
        String format = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        String sql = "select count(*) from datil_table where dateTime BETWEEN '" + format + " 00:00:00' and '" + format + " 23:59:59' GROUP BY userId";
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        int row = 0;
        while (resultSet.next()) {
            row++;
        }
        resultSet.close();
        stmt.close();
        connection.close();
        return row;
    }


    /**
     * 获取订单数据
     */
    public List<HashMap<String, String>> getDatilData(int currPage, int pageSize) throws SQLException {
        String sql = "select datil_table.id as datilId,shop_table.title as title," +
                "shop_table.price as price,shop_table.type as type ," +
                "user_table.userTrueName as userTrueName,user_table.userName as userName,datil_table.count as count," +
                "datil_table.dateTime as datilTime from datil_table JOIN shop_table ON datil_table.shopId = shop_table.id JOIN user_table ON user_table.id = datil_table.userId order by datil_table.id desc limit " + (currPage - 1) * pageSize + ", " + pageSize;
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        System.out.println(sql);
        List<HashMap<String, String>> mapList = new ArrayList<>();
        while (resultSet.next()) {
            HashMap<String, String> map = new HashMap<>();
            map.put("datilId", resultSet.getString("datilId"));
            map.put("shopTitle", resultSet.getString("title"));
            map.put("shopPrice", resultSet.getString("price"));
            map.put("shopType", resultSet.getString("type"));
            map.put("userTrueName", resultSet.getString("userTrueName"));
            map.put("userName", resultSet.getString("userName"));
            map.put("datilTime", resultSet.getString("datilTime"));
            map.put("count", resultSet.getString("count"));
            mapList.add(map);
        }
        resultSet.close();
        stmt.close();
        connection.close();
        return mapList;
    }


    /**
     * 后台首页数据
     */
    public HashMap<String, String> showAdminData() throws SQLException {
        String userCountSql = "select count(*) from user_table where jurisdiction = '普通会员'";
        String prices = "0";
        HashMap<String, String> map = new HashMap<>();
        stmt = connection.prepareStatement(userCountSql);
        resultSet = stmt.executeQuery();
        resultSet.next();
        map.put("user_count", resultSet.getString("count(*)"));
        String adminCount = "select count(*) as count from user_table where jurisdiction = '管理员'";
        stmt = connection.prepareStatement(adminCount);
        resultSet = stmt.executeQuery();
        resultSet.next();
        map.put("admin_count", resultSet.getString("count"));
        String shopCount = "select count(*) as count from shop_table";
        stmt = connection.prepareStatement(shopCount);
        resultSet = stmt.executeQuery();
        resultSet.next();
        map.put("shop_count", resultSet.getString("count"));
        String format = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
        String todayMoney = "select sum(shop_table.price) as price from shop_table join datil_table on datil_table.shopId = shop_table.id where datil_table.dateTime between '" + format + " 00:00:00' and '" + format + " 23:59:59'";
        stmt = connection.prepareStatement(todayMoney);
        resultSet = stmt.executeQuery();
        resultSet.next();
        String price = resultSet.getString("price");
        System.out.println();
        if (price == null) {
            price = "0";
        }
        map.put("today_price", price + "元");
        resultSet.close();
        stmt.close();
        connection.close();
        return map;
    }

    /**
     * 根据ID删除商品
     */
    public boolean delShopItemById(int id) throws SQLException {
        String sql = "delete from shop_table where id = " + id;
        stmt = connection.prepareStatement(sql);
        int i = stmt.executeUpdate();
        stmt.close();
        connection.close();
        return i != 0;
    }

    /**
     * 获取商品名称列表
     */
    public List<HashMap<String, String>> getShopItem() throws SQLException {
        String sql = "select id,title,price,type from shop_table";
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        List<HashMap<String, String>> list = new ArrayList<>();
        while (resultSet.next()) {
            HashMap<String, String> map = new HashMap<>();
            map.put("id", resultSet.getString("id"));
            map.put("title", resultSet.getString("title"));
            map.put("price", resultSet.getString("price"));
            map.put("type", resultSet.getString("type"));
            list.add(map);
        }
        resultSet.close();
        stmt.close();
        connection.close();
        return list;
    }


    /**
     * 添加商品
     */
    public boolean addShop(ShopItemDao dao) throws SQLException {
        String sql = "insert into shop_table values (0,?,?,?,?,?)";
        stmt = connection.prepareStatement(sql);
        stmt.setString(1, dao.getName());
        stmt.setString(2, dao.getPrice());
        stmt.setString(3, "");
        stmt.setString(4, dao.getDateTime());
        stmt.setString(5, dao.getType());
        int i = stmt.executeUpdate();
        stmt.close();
        connection.close();
        return i != 0;
    }


    /**
     * 查询商品数据列表
     */
    public List<HashMap<String, String>> getShopList(String keyWords, int currPage, int pageSize) throws SQLException {
        String sql = "select * from shop_table where title like '%" + keyWords + "%' limit " + currPage + "," + pageSize;
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        List<HashMap<String, String>> mapList = new ArrayList<>();
        while (resultSet.next()) {
            HashMap<String, String> map = new HashMap<>();
            map.put("id", resultSet.getString("id"));
            map.put("title", resultSet.getString("title"));
            map.put("price", resultSet.getString("price"));
            map.put("datetime", resultSet.getString("datetime"));
            map.put("type", resultSet.getString("type"));
            mapList.add(map);
        }
        resultSet.close();
        stmt.close();
        connection.close();
        return mapList;
    }


    /**
     * 查询用户 列表
     */
    public List<HashMap<String, String>> getAllUser(int currPage, int pageSize) throws SQLException {
        currPage = (currPage - 1) * pageSize;
        String sql = "select id,userName,regitTime,userTrueName,jurisdiction,balance from user_table limit " + currPage + ", " + pageSize;
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        List<HashMap<String, String>> mapList = new ArrayList<>();
        while (resultSet.next()) {
            HashMap<String, String> map = new HashMap<>();
            map.put("id", String.valueOf(resultSet.getInt("id")));
            map.put("userName", resultSet.getString("userName"));
            map.put("regitTime", resultSet.getString("regitTime"));
            map.put("userTrueName", resultSet.getString("userTrueName"));
            map.put("jurisdiction", resultSet.getString("jurisdiction"));
            map.put("balance", resultSet.getString("balance"));
            mapList.add(map);
        }
        resultSet.close();
        stmt.close();
        connection.close();
        return mapList;
    }


    /**
     * 查询用户消费记录
     */
    public List<HashMap<String, String>> getUsetLogs(int id) throws SQLException {
        String sql = "SELECT datil_table.count,shop_table.title,shop_table.price,datil_table.dateTime FROM shop_table JOIN datil_table ON " +
                "datil_table.shopId = shop_table.id  where datil_table.userId = " + id + " order by datil_table.id desc";
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        List<HashMap<String, String>> list = new ArrayList<>();
        while (resultSet.next()) {
            HashMap<String, String> map = new HashMap<>();
            map.put("title", resultSet.getString("title"));
            map.put("count", resultSet.getString("count"));
            map.put("price", resultSet.getString("price"));
            map.put("dateTime", resultSet.getString("dateTime"));
            list.add(map);
        }
        resultSet.close();
        stmt.close();
        connection.close();
        return list;
    }

    /**
     * 更新密码
     */
    public boolean updateUserPassword(int id, String newPassword) throws SQLException {
        String sql = "update user_table set userPassword = '" + newPassword + "' where id=" + id;
        stmt = connection.prepareStatement(sql);
        int a = stmt.executeUpdate();
        stmt.close();
        connection.close();
        return a != 0;
    }

    /**
     * 给用户充钱
     */
    public boolean rechargeMoney(int id, int num) throws SQLException {
        String sql = "update user_table set balance = balance+" + num + " where id=" + id;
        stmt = connection.prepareStatement(sql);
        int a = stmt.executeUpdate();
        stmt.close();
        connection.close();
        return a != 0;
    }

    /**
     * 查找用户所有信息
     */
    public HashMap<String, String> getUserAllInfo(int id) throws SQLException {
        String sql = "select id,userName,userPassword,regitTime,userTrueName,jurisdiction,balance from user_table where id=" + id;
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        resultSet.next();
        String userID = resultSet.getString("id");
        String userName = resultSet.getString("userName");
        String regitTime = resultSet.getString("regitTime");
        String userTrueName = resultSet.getString("userTrueName");
        String jurisdiction = resultSet.getString("jurisdiction");
        String balance = resultSet.getString("balance");

        HashMap<String, String> map = new HashMap<>();
        map.put("id", userID);
        map.put("userName", userName);
        map.put("regitTime", regitTime);
        map.put("userTrueName", userTrueName);
        map.put("jurisdiction", jurisdiction);
        map.put("balance", balance);
        resultSet.close();
        stmt.close();
        connection.close();
        return map;
    }

    /**
     * 用户结账
     */
    public boolean userCheckOut(int userid, double price) throws SQLException {
        String sql = "update user_table set balance = balance-" + price + " where id=" + userid;
        stmt = connection.prepareStatement(sql);
        int a = stmt.executeUpdate();
        stmt.close();
        connection.close();
        return a != 0;
    }

    /**
     * 添加订单
     */
    public void updateDatilStatus(HashMap<String, String> datilsList, int UserId) throws SQLException {

        for (Map.Entry<String, String> next : datilsList.entrySet()) {
            String key = next.getKey();
            String value = next.getValue();
            String sql = "insert into datil_table (id,shopId,userId,count,dateTime) values (0,?,?,?,?)";
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String dateTime = sdf.format(new Date());
            stmt = connection.prepareStatement(sql);
            stmt.setString(1, key);
            stmt.setString(2, String.valueOf(UserId));
            stmt.setString(3, value);
            stmt.setString(4, dateTime);
            int i = stmt.executeUpdate();
        }

        stmt.close();
        connection.close();
    }


    /**
     * 查询商品的价格 和 名称
     */
    public HashMap<String, String> queryNameAndPrice(int id) throws SQLException {
        String sql = "select price,title from shop_table where id=" + id;
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        resultSet.next();
        HashMap<String, String> map = new HashMap<>();
        map.put("price", resultSet.getString("price"));
        map.put("title", resultSet.getString("title"));
        resultSet.close();
        stmt.close();
        connection.close();
        return map;
    }

    /**
     * 查询用户余额
     */
    public double queryUserBalance(int id) throws SQLException {
        String sql = "select balance from user_table where id = " + id;
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        resultSet.next();
        double price = Double.parseDouble(resultSet.getString("balance"));
        resultSet.close();
        stmt.close();
        connection.close();
        return price;
    }

    /**
     * 查询用户id
     */
    public HashMap<String, Object> queryUserPrice(String username) throws SQLException {
        String sql = "select id,jurisdiction from user_table where userName = '" + username + "'";
        HashMap<String, Object> map = new HashMap<>();
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        resultSet.next();
        int id = new Integer(resultSet.getString("id"));
        String jurisdiction = resultSet.getString("jurisdiction");
        map.put("id", id);
        map.put("jurisdiction", jurisdiction);
        resultSet.close();
        stmt.close();
        connection.close();
        return map;
    }

    /**
     * 根据id查询价格
     */
    public double quaryPriceById(int id) throws SQLException {
        String sql = "select price from shop_table where id = " + id;
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        resultSet.next();
        double price = Double.parseDouble(resultSet.getString("price"));
        resultSet.close();
        stmt.close();
        connection.close();
        return price;
    }

    /**
     * 商品查询显示
     */
    public List<HashMap<String, String>> getShop(String type) throws SQLException {
        String sql = "select id,title,price,image from shop_table where type = '" + type + "'";
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        List<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
        while (resultSet.next()) {
            HashMap<String, String> map = new HashMap<>();
            map.put("id", resultSet.getString("id"));
            map.put("title", resultSet.getString("title"));
            map.put("price", resultSet.getString("price"));
            map.put("image", resultSet.getString("image"));
            list.add(map);
        }
        resultSet.close();
        stmt.close();
        connection.close();
        return list;
    }

    /**
     * 用户登录
     */
    public String userLogin(String username, String password) throws Exception {
        String sql = "select * from user_table where userName = '" + username + "'";
        stmt = connection.prepareStatement(sql);
        resultSet = stmt.executeQuery();
        String tempStr = "";
        while (resultSet.next()) {
            String userPassword = resultSet.getString("userPassword");
            String userTrueName = resultSet.getString("userTrueName");
            if (password.equals(userPassword)) {
                tempStr = userTrueName;
                break;
            }
        }
        resultSet.close();
        stmt.close();
        connection.close();
        return tempStr;
    }

    public void insetSql(String sql) throws SQLException {
        stmt = connection.prepareStatement(sql);
        stmt.executeUpdate();
        close();
    }


    public void close() {
        try {
            stmt.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
